<?php

class YuiController extends CExtController {
    public function actionTreeload() {
        $data = (null===$_GET['table'])? 0 : $this->_loadTreeAsync($_GET['table'], $_GET["id"]);
        $this->renderPartial('asyncView', $data);
    }
    public function actionTreeloadfull() {
        $data = (null===$_GET['table'])? 0 : $this->_loadTreeSync($_GET['table']);
        $this->renderPartial('asyncView', $data);
    }
    public function actionTreecurrentpath() {
        $table = $_GET['table'];
        $from = $_GET["from"];
        $data = '';
        if( (null === $table) || (null === $from) )
        $data = 0;
        else try
        {
            while($from != 0)
            {
                //if(($data != '')&&($from > 0)) $data .= ', ';
                $sql = 'select parentId from `'.$table.'` where id='.$from;
                $command = Yii::app()->db->createCommand($sql);
                $from = $command->queryScalar();
                if($from > 0) $data .= $from.',';
            }
            $data = '['.$data.']';
        }
        catch(Exception $e) { $data = 0; }
        $this->renderPartial('asyncView', $data);
    }
    public function actionTreeupdate() {
        $data = 1;
        if( (null===$_GET['table']) or (null===$_GET['id']) ) $data = 0;
        else {
            $sql = 'update `'.$_GET['table'].'` set name = "'.$_GET['name'].'" where id = '.$_GET['id'];
            $command = Yii::app()->db->createCommand($sql);
            try { $command->execute(); }
            catch(Exception $e) { $data = 0; }
        }
        $this->renderPartial('asyncView', $data);
    }
    public function actionTreedelete() {
        $table=$_GET['table'];
        $id=$_GET['id'];
        $data = 0;
        if( (null!=$table) or (null!=$id) ) {
            $sql = 'select count(*) from `'.$table.'` where parentId = '.$id;
            $command = Yii::app()->db->createCommand($sql);
            try {
                $childCount = $command->queryScalar();
                if($childCount == 0) {
                    $sql = 'delete from `'.$table.'` where id = '.$id;
                    $command = Yii::app()->db->createCommand($sql);
                    $command->execute();
                    $data = 1;
                }
            }
            catch(Exception $e) { $data = 0; }
        }
        $this->renderPartial('asyncView', $data);
    }
    public function actionTreecreate() {
        $data = 0;
        $table = $_GET['table'];
        $parentId = $_GET['parentId'];
        $name = $_GET['name'];
        if( (null===$table) or (null===$parentId) ) $data = 0;
        else {
            $sql = 'insert into `'.$table.'` (name, parentId) values("'.$name.'", '.$parentId.')';
            $command = Yii::app()->db->createCommand($sql);
            try {
                $command->execute();
                $sql = 'select id from `'.$table.'` where name = "'.$name.'" and parentId = '.$parentId;
                $command = Yii::app()->db->createCommand($sql);
                $data = $command->queryScalar();
            }
            catch(Exception $e) { $data = 0; }
        }
        $this->renderPartial('asyncView', $data);
    }
    public function actionTreemove() {
        $data = 1;
        if( (null===$_GET['table']) or (null===$_GET['id']) or (null===$_GET['parentId']) ) $data = 0;
        else {
            $sql = 'update `'.$_GET['table'].'` set parentId = '.$_GET['parentId'].' where id = '.$_GET['id'];
            $command = Yii::app()->db->createCommand($sql);
            try { $command->execute(); }
            catch(Exception $e) { $data = 0; }
        }
        $this->renderPartial('asyncView', $data);
    }

    public function actionAutocomplete() {
        $data = '';
        $field = $_GET['field'];
        if( (null != $_GET['table']) and (null != $field) )
        {
            $sql = 'select '. $field.' from '.$_GET['table'].' where '.$field.' LIKE "'.$_GET['query'].'%"';
            $command = Yii::app()->db->createCommand($sql);
            $rows = $command->queryAll();
            $data = '{"Result":'.json_encode($rows).'}';
        }
        $this->renderPartial('asyncView', $data);
    }
    public function actionDatatable() {
        $data = array();
        if(isset($_GET['table']))
        {
            $table = $_GET['table'];
            $connection = Yii::app()->db;
            if(isset($_REQUEST['action'])) // execute table operations
            {
                $action = $_REQUEST['action'];
                $pkname = $_REQUEST['pkname'];
                switch($action)
                {
                    case 'edit':
                        $column = $_REQUEST['column'];
                        $value = $_REQUEST['value'];
                        $pkvalue = $_REQUEST['pkvalue'];
                        $sqlText = 'update `'.$table.'` set '.$column.'='.$value.' where '.$pkname.'='.$pkvalue;
                        break;
                    case 'insert':
                        $sqlText = 'insert into '.$table.' values()';
                        break;
                    case 'delete':
                        $pkvalue = $_REQUEST['pkvalue'];
                        $sqlText = 'delete from '.$table.' where '.$pkname.'='.$pkvalue;
                        break;
                }
                try
                {
                    $connection->createCommand($sqlText)->execute();
                    if($action == 'insert')
                    {
                        $sqlText = 'select * from '.$table.' where '.$pkname.' = '.$connection->getLastInsertID();
                        $row = $connection->createCommand($sqlText)->queryRow();
                        $this->renderPartial('asyncView', json_encode($row));
                    }
                    else
                        $this->renderPartial('asyncView', 1);
                }
                catch(Exception $e) // an exception is raised if a query fails
                {
                    $this->renderPartial('asyncView', 0);
                }
            }
            else // select records from table
            {
                $from = $_REQUEST['from'];
                $size = $_REQUEST['size'];
                $sortBy = isset($_REQUEST['sortBy'])?$_REQUEST['sortBy']:"id";
                $sortDir = isset($_REQUEST['sortDir'])?$_REQUEST['sortDir']:"";
                $sqlGetData = 'select * from '.$table.' order by '.$sortBy.' '.$sortDir.' limit '.$from.', '.$size;
                $sqlGetCount = 'select count(*) from '.$table;
                $transaction=$connection->beginTransaction();
                try
                {
                    $rows = $connection->createCommand($sqlGetData)->queryAll();
                    $recCount = $connection->createCommand($sqlGetCount)->queryScalar();
                    $transaction->commit();
                }
                catch(Exception $e) // an exception is raised if a query fails
                {
                    $transaction->rollBack();
                }
                $data = array('totalRecords'=>$recCount, 'records'=>$rows);
                $this->renderPartial('asyncView', json_encode($data));
            }
        }
        else $this->renderPartial('asyncView', 0); // table name is empty
    }
    public function actionLookup() {
        $data = '';
        if(null != $_GET['table'])
        {
            $fieldId = $_POST['fieldId'];
            $fieldLabel = $_POST['fieldLabel'];
            $sql = 'select '. $fieldLabel.' as label, '.$fieldId.' as value from '.$_GET['table'].' order by '.$fieldLabel;
            $command = Yii::app()->db->createCommand($sql);
            $rows = $command->queryAll();
            $data = json_encode($rows);
        }
        $this->renderPartial('asyncView', $data);
    }
    /************************************************************************
     * DB-related functions for AJAX request's
     ************************************************************************/
    private function _loadTreeAsync($table, $id) {
        $rows = $this->_loadTreeOneLevel($table, $id);
        $data = '';
        foreach($rows as $row) {
            $leaf = $row['childs']? 0 : 1;
            $data .= '{ label: "'.$row['name'].'",title : "'.$row['name'].'",isLeaf : '.$leaf.', data : {id : '.$row['id'].'}},';
        }
        return '['.$data.']';
    }
    private function _loadTreeSync($table) {
        $sql = 'select * from `'.$table.'` a
                left join
                (select parentId as pid, count(*) as childs from `'.$table.'` group by parentId) b
                on a.id = b.pid
                order by parentId, id';
        $command = Yii::app()->db->createCommand($sql);
        $rows = $command->queryAll();
        $data = '';
        foreach($rows as $row) {
            $leaf = $row['childs']? 0 : 1;
            $data .= '{ label: "'.$row['name'].'",title : "'.$row['name'].'",isLeaf : '.$leaf.', data : {id : '.$row['id'].', parentId: '.$row['parentId'].'}},';
        }
        return '['.$data.']';
    }
    private function _loadTreeOneLevel($table, $level) {
        $sql = 'select * from `'.$table.'` a
                left join
                (select parentId as pid, count(*) as childs from `'.$table.'` group by parentId) b
                on a.id = b.pid
                where a.parentId = '.$level.
                ' order by name';
        $command = Yii::app()->db->createCommand($sql);
        return $command->queryAll();
    }
}
?>
